package com.xhcl.common.utils;



import java.math.BigDecimal;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class POIUtil
{
  public static final String[] columName = { "A", "B", "C", "D", "E", "F", 
    "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", 
    "T", "U", "V", "W", "X", "Y", "Z" };
  public static final String text = "text";
  public static final String money = "(#,##0.00_);[Red](#,##0.00)";
  public static final String Bigmoney = "(#,##0_);[Red](#,##0)";
  public static final String date = "yyyy-m-d";
  public static final String percent = "0.00%";

  
	public static HSSFRow getRow(int rowIndex, HSSFSheet sheet)
	{
			HSSFRow row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		return row;
	}

    public static HSSFCell getCell(HSSFRow row, int columnIndex)
	{
    	
    	HSSFCell cell = row.getCell(columnIndex);
		if (cell == null) {
			cell = row.createCell(columnIndex);
		}
		return cell;
		}


  
  public static void setCellValue(HSSFSheet sheet, int rowNum, short colNum, String value, HSSFCellStyle style)
  {
    HSSFRow row = getRow(rowNum, sheet);
    HSSFCell cell = getCell(row, colNum);
    cell.setCellValue(value);
    cell.setCellStyle(style);
  }

  public static void setCellFormula(HSSFSheet sheet, int rowNum, short colNum, String Formula, HSSFCellStyle style)
  {
    HSSFRow row = getRow(rowNum, sheet);
    HSSFCell cell = getCell(row, colNum);
    cell.setCellType(CellType.NUMERIC);
    cell.setCellFormula(Formula);
    cell.setCellStyle(style);
  }

  public static void setCellValue(HSSFSheet sheet, int rowNum, short colNum, Date value, HSSFCellStyle style)
  {
    HSSFRow row = getRow(rowNum, sheet);
    HSSFCell cell = getCell(row, colNum);
    cell.setCellValue(value);
    cell.setCellStyle(style);
  }

  public static void setCellValue(HSSFSheet sheet, int rowNum, short colNum, double value, HSSFCellStyle style)
  {
    HSSFRow row = getRow(rowNum, sheet);
    HSSFCell cell = getCell(row, colNum);
    cell.setCellType(CellType.NUMERIC);
    cell.setCellValue(value);
    cell.setCellStyle(style);
  }

  public static void setCellValue(HSSFSheet sheet, int rowNum, short colNum, BigDecimal value, HSSFCellStyle style)
  {
    HSSFRow row = getRow(rowNum, sheet);
    HSSFCell cell = getCell(row, colNum);
    cell.setCellType(CellType.NUMERIC);
    cell.setCellValue(value == null ? 0.0D : value.doubleValue());
    cell.setCellStyle(style);
  }

  public static HSSFCellStyle creatStyle(HSSFWorkbook workbook, short align, String font, int size, boolean isBold, boolean isBorders)
  {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(creatFont(workbook, font, size, isBold));
    style.setAlignment(HorizontalAlignment.LEFT); 
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setWrapText(true);
    return style;
  }

  public static HSSFCellStyle creatStyle(HSSFWorkbook workbook, short align, short valign, String font, int size, boolean isBold, boolean isBorders)
  {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(creatFont(workbook, font, size, isBold));
    style.setAlignment(HorizontalAlignment.LEFT); 
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setWrapText(true);
    return style;
  }

  public static HSSFFont creatFont(HSSFWorkbook workbook, String font, int size, boolean isBold)
  {
    HSSFFont hssfFont = workbook.createFont();
    hssfFont.setFontName(font);
    hssfFont.setFontHeightInPoints((short)size);
    if (isBold) {
      hssfFont.setBold(true);
    }
    return hssfFont;
  }
}